﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TaxSort
{
    class DataLoader
    {
        mainForm myForm; //主界面
        string path; //路径
        DataGridView dgv;
        OleDbConnection thisConnection;
        OleDbCommand thisCommand;
        delegate void SetTextCallback(string status,int done,int total,string [] rows);//设置委托
        //初始化
        public DataLoader(mainForm _myForm,string _path,DataGridView _dgv)
        {
            myForm = _myForm;
            path = _path;
            dgv=_dgv;
            thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb");
        }
        public void Load()
        {
            try
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                if (excel == null)
                {
                    MessageBox.Show("无法创建Excel对象,可能您的计算机未安装Excel!");
                    return;
                }
                excel.Visible = false;
                excel.UserControl = true;
                object oMissiong = System.Reflection.Missing.Value;
                excel.Application.Workbooks.Open(path);
                //取第一个sheet
                Microsoft.Office.Interop.Excel.Worksheet worksheet=(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets.get_Item(1);
                //统计行数，去掉第一行标题行
                int rowCount = worksheet.UsedRange.Cells.Rows.Count-1;
                int colCount = worksheet.UsedRange.Cells.Columns.Count;
                int minCount = colCount > dgv.Columns.Count ? dgv.Columns.Count : colCount;
                string[] row_data= new string[minCount];
                //取第二行开始的所有数据，结束应改为rowcount+1，因为跳过了标题行，而数据行数是rowcount行
                Range rng1 = worksheet.Cells.get_Range(int2ASCII(1)+"2", int2ASCII(minCount)+ (rowCount+1)); 
                //把数据内容转换为arrayItem二维数组
                object[,] arryItem = (object[,])rng1.Value2;   //get range's value
                //打开数据连接，准备存入数据库
                thisConnection.Open();
                thisCommand = thisConnection.CreateCommand();
                //先删除原有数据
                clearDB();
                //按每行数据进行处理
                for (int i = 1; i <=rowCount; i++)
                {
                    for(int j=1;j<=minCount;j++){
                        //arrayItem是从arrayItem[1,1]开始
                        row_data[j-1] = arryItem[i,j].ToString();
                    }
                    save2DB(row_data);
                    setWorkStatus("第"+i+"行",i, rowCount,row_data);  
                }
                //关闭链接
                if (thisConnection.State == System.Data.ConnectionState.Open)
                    thisConnection.Close();
                excel.Quit();
                excel = null;
                /*其实不用杀死进程，当程序退出时候自动会结束进程的，杀死反而会造成excel的未保存错误
                Process[] procs = Process.GetProcessesByName("excel");
                foreach (Process pro in procs)
                {
                    pro.Kill();//没有更好的方法,只有杀掉进程
                }
                 */
                GC.Collect();
            }
            catch (Exception ex)
            {
                setWorkStatus("提取出错！"+ex.Message.ToString(), 0,0,null);
            }            
        }
        //清空数据库
        public void clearDB()
        {
            string sql = "delete from tax";
            thisCommand.CommandText = sql;
            thisCommand.ExecuteNonQuery();
        }
        /// <summary>
        /// 保存到数据库
        /// </summary>
        public void  save2DB(string[] rows){
            try
            {
                string sql = "insert into tax(人员编码,姓名,证件号码,应发合计,养老保险,医疗保险,失业保险,公积金,社保调整,缴费合计,年金抵税金额,计税工资,本次扣税)";
                sql += " values('";
                for (int j = 0; j < 3; j++)
                {
                    string values = rows[j];
                    sql += values + "','";
                }
                sql = sql.Substring(0, sql.Length - 1);
                for (int j = 3; j < rows.Length; j++)
                {
                    string values = rows[j];
                    sql += values + ",";

                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += ")";
                thisCommand.CommandText = sql;
                thisCommand.ExecuteNonQuery();
                
            }
            catch (Exception ex)
            {
                setWorkStatus("保存到Access数据库出错！" + ex.Message.ToString(), 0, 0, null);
            }
        }
        public void setWorkStatus(string status,int done,int total,string[] rows)
        {
            //来设置工作状态，解决多线程下不能设置问题
            try
            {
                if (myForm.InvokeRequired)
                {
                    SetTextCallback d = new SetTextCallback(setWorkStatus);
                    myForm.Invoke(d, new object[] { status, done,total,rows });

                }
                else
                {
                    if (total == 0)
                    {
                        myForm.tsslStatus.Text = status;
                    }
                    else
                    {
                        //添加到dgv中
                        dgv.Rows.Add(rows);

                        if (total == done)
                        {
                            myForm.tsslStatus.Text = "载入已完成，共有" + total + "条记录,应发合计" + getNeedSummary();
                            showIndex(dgv);
                        }
                        else
                            myForm.tsslStatus.Text = status + "正在载入，完成" + done + "/" + total + "。";
                    }
                }
            }
            catch (Exception ex)
            {
                string info = ex.Message + ex.Data;

                MessageBox.Show(info);

            }

        }
        /// <summary>
        /// 将数字序号转换成大写字母序号，字母从A开始。
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        private string int2ASCII(int number)
        {
            number = number + 64; //从大写字母A开始
            byte[] array = new byte[1];
            array[0] = (byte)(Convert.ToInt32(number));//ASCII码强制转换二进制
            return Convert.ToString(System.Text.Encoding.ASCII.GetString(array));
        }
        /// <summary>
        /// 从数据库载入已有数据
        /// </summary>
        public void loadDB()
        {
            thisConnection.Open();
            thisCommand = thisConnection.CreateCommand();
            thisCommand.CommandText = "SELECT * FROM TAX ";
            OleDbDataReader thisReader = thisCommand.ExecuteReader();
            int count = 0;
            while (thisReader.Read())
            {
                count++;
                ArrayList al = new ArrayList();
                for (int i = 0; i < 12; i++)
                {
                    al.Add(thisReader[i].ToString());
                }
                string[] arrString = (string[])al.ToArray(typeof(string));
                dgv.Rows.Add(arrString);
            }
            thisReader.Close();
            thisConnection.Close();
            showIndex(dgv);
            myForm.tsslStatus.Text = "载入已完成，共有" + count + "条记录,应发合计" + getNeedSummary();
         
        }
        /// <summary>
        /// 计算
        /// </summary>
        public  void compute(double limit=3268.38)
        {
            
            thisConnection.Open();
            thisCommand = thisConnection.CreateCommand();
            //公积金大于限额的设置为限额,应发合计相应减少
            thisCommand.CommandText = "UPDATE TAX SET 公积金="+limit+" ,应发合计=应发合计-(公积金-"+limit+")where 公积金>"+limit;
            thisCommand.ExecuteNonQuery();

            //计税工资中如果是负数，则设置为0,并设置 应发合计=养老保险+医疗保险+失业保险+公积金
            thisCommand.CommandText = "UPDATE TAX SET 计税工资=0,应发合计=养老保险+医疗保险+失业保险+公积金 where 计税工资<0";
            thisCommand.ExecuteNonQuery();

            //如果医疗保险=社保调整，则两者都为0
            thisCommand.CommandText = "UPDATE TAX SET 社保调整=0,医疗保险=0 where 社保调整=医疗保险";
            thisCommand.ExecuteNonQuery();

            thisCommand.CommandText = "UPDATE TAX SET 计税工资=0,应发合计=-计税工资 where 计税工资<0";
            thisCommand.ExecuteNonQuery();

            thisConnection.Close();
        }
        /// <summary>
        /// 应发合计统计
        /// </summary>
        /// <returns></returns>
        public string getNeedSummary()
        {
            if (thisConnection.State == System.Data.ConnectionState.Closed)
                thisConnection.Open();
            thisCommand = thisConnection.CreateCommand();
            thisCommand.CommandText = "SELECT SUM(应发合计) AS  TOTAL FROM TAX ";
            string summary = thisCommand.ExecuteScalar().ToString();
            thisConnection.Close();
            return summary;
        }
        /// <summary>
        /// 导出到excel文件
        /// </summary>
        public void save2Excel()
        {
            string title = "个税计算";
            string fileName = "";//保存的excel文件名
            bool isShowExcel = false;//是否显示Excel
            /*保存对话框*/
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "导出Excel(*.xls)|*.xls";
            sfd.FileName = title + DateTime.Now.ToString("yyyyMMddhhmmss");

            if (sfd.ShowDialog() == DialogResult.OK)
            {
                fileName = sfd.FileName;
                /*建立Excel对象*/
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                if (excel == null)
                {
                    MessageBox.Show("无法创建Excel对象,可能您的计算机未安装Excel!");
                    return;
                }
                try
                {
                    myForm.tsslStatus.Text = "正在导出...";
                    excel.Application.Workbooks.Add(true);
                    excel.Visible = isShowExcel;
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
                    worksheet.Cells[1, 1] = "工号";
                    worksheet.Cells[1, 2] = "姓名";
                    worksheet.Cells[1, 3] = "证照类型";
                    worksheet.Cells[1, 4] = "证照号码";
                    worksheet.Cells[1, 5] = "本期收入";
                    worksheet.Cells[1, 6] = "本期免税收入";
                    worksheet.Cells[1, 7] = "基本养老保险费";
                    worksheet.Cells[1, 8] = "基本医疗保险费";
                    worksheet.Cells[1, 9] = "失业保险费";
                    worksheet.Cells[1, 10] = "住房公积金";


                    thisConnection.Open();
                    thisCommand = thisConnection.CreateCommand();
                    thisCommand.CommandText = "SELECT * FROM TAX ";
                    OleDbDataReader thisReader = thisCommand.ExecuteReader();
                    int row_index = 2;
                    while (thisReader.Read())
                    {
                        worksheet.Cells[row_index, 1] = thisReader["人员编码"].ToString().Replace('z','\'');
                        worksheet.Cells[row_index, 2] = "'" + thisReader["姓名"].ToString();
                        worksheet.Cells[row_index, 3] = "'居民身份证";
                        worksheet.Cells[row_index, 4] = "'" + thisReader["证件号码"].ToString();
                        worksheet.Cells[row_index, 5] = thisReader["应发合计"].ToString();
                        worksheet.Cells[row_index, 7] = thisReader["养老保险"].ToString();
                        worksheet.Cells[row_index, 8] = thisReader["医疗保险"].ToString();
                        worksheet.Cells[row_index, 9] = thisReader["失业保险"].ToString();
                        worksheet.Cells[row_index, 10] = thisReader["公积金"].ToString();
                        row_index++;
                    }
                    thisReader.Close();
                    thisConnection.Close();
                    worksheet.SaveAs(fileName, 56);//56为兼容模式，不加参数默认为当前xlsx格式。
                    MessageBox.Show("成功导出到" + fileName);
                    myForm.tsslStatus.Text = "导出完成！";
                }
                catch { }
                finally
                {
                    excel.Quit();
                    excel = null;
                    GC.Collect();
                }
            }
            return;
        }
        /// <summary>
        /// 显示datagridview的行标
        /// </summary>
        /// <param name="dgv">datagridview对象</param>
        public void showIndex(DataGridView dgv)
        {
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                dgv.Rows[i].HeaderCell.Value = (i + 1).ToString();  //HeaderCell获取或设置行的标头单元格             
            }
            dgv.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToDisplayedHeaders;
        }

    }


    
}
